﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Script.Serialization
Imports Newtonsoft.Json
Imports HgCalcExp
Imports CrystalDecisions.CrystalReports.Engine
Imports hg3.hg3

Public Class DbHelper

#Region "Login"
	Public Shared Function checkLogin(ByVal username As String, ByVal password As String) As String
		Dim pass As String = Encrypt.Encrypt(password, username)
		Dim sqlCommand As String = "SELECT * FROM UserInfo WHERE Password = '" & pass & "'"
		Dim dt As DataTable = returnDataSet(sqlCommand).Tables(0)
		If dt.Rows.Count < 1 Then
			Return "*"
		End If
		Return ParserJSON.DataTableParserJSON(dt)
	End Function
#End Region

#Region "getData"
	Public Shared Function getData(ByVal columnlist As String, ByVal tablename As String, ByVal condition As String) As String
		Dim sqlCommand As String = "SELECT " & columnlist & " FROM " & tablename & " WHERE " & condition
		Return ParserJSON.DataTableParserJSON(returnDataSet(sqlCommand).Tables(0))
	End Function

	Public Shared Function getData2(ByVal sqlcom As String) As String		
		Return ParserJSON.DataTableParserJSON(returnDataSet(sqlcom).Tables(0))
	End Function
#End Region

#Region "Stock"
	Public Shared Function getStock() As String
		Dim sqlCommand As String = "SELECT * FROM dmkho"
		Return ParserJSON.DataTableParserJSON(returnDataSet(sqlCommand).Tables(0))
	End Function
#End Region

#Region "Utils"
	Private Shared Function getConnection() As SqlConnection
		Dim cnnString As String = ConfigurationManager.ConnectionStrings("SQLConnectionString").ConnectionString
		Return New SqlConnection(cnnString)
	End Function

	Private Shared Function returnDataSet(ByVal sqlcommand As String) As DataSet
		Dim ds As New DataSet
		Dim cnn As SqlConnection = getConnection()
		Dim command As New SqlCommand(sqlcommand, cnn)
		Dim adapter As New SqlDataAdapter
		adapter.SelectCommand = command
		adapter.Fill(ds)
		adapter.Dispose()
		command.Dispose()
		cnn.Close()
		cnn.Dispose()
		Return ds
	End Function

	Public Shared Function executeCommand(ByVal sqlcommand As String) As String
		Dim cnn As SqlConnection = getConnection()
		cnn.Open()
		Dim command As New SqlCommand(sqlcommand, cnn)
		Dim i As Integer = command.ExecuteNonQuery()
		command.Dispose()
		cnn.Close()
		cnn.Dispose()
		Dim sqlCommand2 As String = "SELECT * FROM dmstt"
		Dim s As String = ParserJSON.DataTableParserJSON(returnDataSet(sqlCommand2).Tables(0))
		Return s
	End Function
#End Region

#Region "TEST"
	Public Shared Function Test() As String
		Return "HIVIJSC"
	End Function
#End Region


End Class
